package tr.edu.metu.is.mega.server.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import tr.edu.metu.is.mega.common.dto.ActivityType;
import tr.edu.metu.is.mega.common.dto.SubmissionViewDTO;
import tr.edu.metu.is.mega.server.core.AbstractDAO;
import tr.edu.metu.is.mega.server.entity.Submissionview;

/**
 * DAO implementation for Submissionview entity.
 */
public class SubmissionViewDAO extends AbstractDAO<Submissionview, Integer> {

    public List<SubmissionViewDTO> querySubmissions(int courseid, int teamid) {
        String sql = "SELECT a.pkid as activityid, a.description as activitydescription,a.duedate as duedate, a.submissiontype as submissiontype,";
        sql += "s1.pkid as submissionid,s1.submissiondate as submissiondate, s1.filepath as submissionfile,t2.pkid as teamid, r.fk_reviewerteamid as reviewteamid,t2.teamname as teamname,";
        sql += "u1.username as submitter,t1.teamname as reviewteam, s2.submissiondate as reviewdate, s2.filepath as reviewfile FROM";
        sql += " activity a";
        sql += " left join reviewer r on (a.pkid=r.fk_activityid and r.fk_teamid=" + teamid + ")";
        sql += " left join team t1 on (r.fk_reviewerteamid=t1.pkid)";
        sql += " left join submission s1 on (a.pkid=s1.fk_activity and s1.fk_submitterteam=" + teamid + ")";
        sql += " left join systemuser u1 on (s1.fk_submittedby=u1.pkid)";
        sql += " left join submission s2 on (s2.fk_relatedsubmission=s1.pkid)";
        sql += " left join team t2 on (s1.fk_submitterteam=t2.pkid)";
        sql += " where a.fk_courseid=" + courseid;
        System.out.println(sql);
        Connection con = DatabaseConnectionFactory.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        List<SubmissionViewDTO> list = new ArrayList<SubmissionViewDTO>();
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                SubmissionViewDTO dto = new SubmissionViewDTO();
                // int activityid;
                dto.setActivityid(rs.getInt("activityid"));
                // String activitydescription;
                dto.setActivitydescription(rs.getString("activitydescription"));
                // Date duedate;
                java.sql.Timestamp d = rs.getTimestamp("duedate");
                if (d != null) {
                    dto.setDuedate(new Date(d.getTime()));
                }
                // ActivityType submissiontype;
                dto.setSubmissiontype(ActivityType.findByCode(rs.getString("submissiontype")));
                // int submissionid;
                dto.setSubmissionid(rs.getInt("submissionid"));
                // Date submissiondate;
                d = rs.getTimestamp("submissiondate");
                if (d != null) {
                    dto.setSubmissiondate(new Date(d.getTime()));
                }
                // String submissionfile;
                dto.setSubmissionfile(rs.getString("submissionfile"));
                // int teamid;
                dto.setTeamid(rs.getInt("teamid"));
                // int reviewteamid;
                dto.setReviewteamid(rs.getInt("reviewteamid"));
                // String teamname;
                dto.setTeamname(rs.getString("teamname"));
                // String submitter;
                dto.setSubmitter(rs.getString("submitter"));
                // String reviewteam;
                dto.setReviewteam(rs.getString("reviewteam"));
                // Date reviewdate;
                d = rs.getTimestamp("reviewdate");
                if (d != null) {
                    dto.setReviewdate(new Date(d.getTime()));
                }
                // reviewfile
                dto.setReviewFile(rs.getString("reviewfile"));
                list.add(dto);
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

}
